<?php
/**
*小区经理市场数据分析
*/
require("../include/init.php");

$mysql=mysql::getIns();

date_default_timezone_set('Asia/Shanghai');

$sql="select areaname from area";
$area=$mysql->getAll($sql);
foreach($area as $v){
  $address=$v['areaname'];
  $maxtime=time();
  $mintime=$maxtime-10*60;
  // echo $maxtime . '<br />';
  // echo $mintime;
  $sql="select count(*) from alldev where whitelist <> '1' and isOL=1 and timestamp > $mintime and timestamp < $maxtime and shop='". $address ."'";
  $allnow=$mysql->getOne($sql);
  // echo $allnow;


  //统计当天累计人数数
  $t = time();
  $start = mktime(0,0,0,date("m",$t),date("d",$t),date("Y",$t));
  $end = mktime(23,59,59,date("m",$t),date("d",$t),date("Y",$t));
  $sql="select count(*) from alldev where whitelist <> '1' and timestamp > $start and timestamp < $end and shop='" . $address . "'";
  $allday=$mysql->getOne($sql);



  //统计昨天累计人数
  //昨天的时间戳
  $t = time();
  $start = mktime(0,0,0,date("m",$t),date("d",$t),date("Y",$t))-24*3600;
  $end = mktime(23,59,59,date("m",$t),date("d",$t),date("Y",$t))-24*3600;

  $sql="select count(*) from alldev where whitelist <> '1' and timestamp > $start and timestamp < $end and shop='" . $address . "'";
  $yesdallday=$mysql->getOne($sql);



  /****************
  *获取本周开始时间和结束时间
  *
  ***********/

  $date=date('Y-m-d');  //当前日期
  $first=1; //$first =1 表示每周星期一为开始日期 0表示每周日为开始日期
  $w=date('w',strtotime($date));  //获取当前周的第几天 周日是 0 周一到周六是 1 - 6
  $now_start=date('Y-m-d',strtotime("$date -".($w ? $w - $first : 6).' days')); //获取本周开始日期，如果$w是0，则表示周日，减去 6 天
  $now_end=date('Y-m-d',strtotime("$now_start +6 days"));  //本周结束日期
  $one=strtotime($now_start);
  $two=$one+24*3600*1;
  $three=$one+24*3600*2;
  $four=$one+24*3600*3;
  $five=$one+24*3600*4;
  $six=$one+24*3600*5;
  $seven=$one+24*3600*6;
  $nowone=$one+24*3600*7;


  //统计本周周一进店量
  $sql="select count(*) from alldev where whitelist <> '1' and timestamp >= $one and timestamp < $two and shop='". $address ."'";
  $onejk=$mysql->getOne($sql);
  //统计本周周二进店量
  $sql="select count(*) from alldev where whitelist <> '1' and timestamp >= $two and timestamp < $three and shop='". $address ."'";
  $twojk=$mysql->getOne($sql);
  //统计本周周三进店量
  $sql="select count(*) from alldev where whitelist <> '1' and timestamp >= $three and timestamp < $four and shop='". $address ."'";
  $threejk=$mysql->getOne($sql);
  //统计本周周四进店量
  $sql="select count(*) from alldev where whitelist <> '1' and timestamp >= $four and timestamp < $five and shop='". $address ."'";
  $fourjk=$mysql->getOne($sql);
  //统计本周周五进店量
  $sql="select count(*) from alldev where whitelist <> '1' and timestamp >= $five and timestamp < $six and shop='". $address ."'";
  $fivejk=$mysql->getOne($sql);
  // echo $fivejk;
  // exit;
  //统计本周周六进店量
  $sql="select count(*) from alldev where whitelist <> '1' and timestamp >= $six and timestamp < $seven and shop='". $address ."'";
  // $sql="select * from alldev where whitelist <> '1' and timestamp >= $six and timestamp < $seven and shop='". $address ."'";
  $sixjk=$mysql->getOne($sql);
  // echo $sixjk;
  // exit;
  //统计本周周日进店量
  $sql="select count(*) from alldev where whitelist <> '1' and timestamp >= $seven and timestamp < $nowone and shop='". $address ."'";
  $sevenjk=$mysql->getOne($sql);


  //统计本月人数

  /**************************
  获取本月第一个周一的时间

  **********************/
  $year=date('Y',time());
  $month=date('m',time());
  $base_time = strtotime ( "{$year}-{$month}-01" );
  $base_w = date ( "w", $base_time );

  if($base_w == '1'){
    //刚好是星期一
    $first = $base_time;
  }else{
    //不是星期一
    //找到上个星期天是几月几号
    $last_sun = date ( "Y-m-d", strtotime("-$base_w day", $base_time));
    //在上个星期天的基础上 加8天
    $first = strtotime ( "+8 day", strtotime ( $last_sun ) );
  }
  //第二个周一的时间
  $second=$first+7*24*3600;
  // echo date('Y-m-d H:i:s',$second);
  // exit;
  //第三个时间戳
  $third=$second+7*24*3600;
  // echo date('Y-m-d H:i:s',$third);
  // exit;
  //第四个时间戳
  $fourth=$third+7*24*3600;
  // echo date('Y-m-d H:i:s',$fourth);
  // exit;
  //下周一时间戳
  $fifth=$fourth+7*24*3600;
  // echo date('Y-m-d H:i:s',$third);
  // exit;


  //获取这个月第一周的人数
  $sql="select count(*) from alldev where whitelist <> '1' and timestamp >= $first and timestamp < $second and shop='". $address ."'";
  $firstjk=$mysql->getOne($sql);
  // echo $firstjk;
  // exit;

  //获取这个月第二周的人数
  $sql="select count(*) from alldev where whitelist <> '1' and timestamp >= $second and timestamp < $third and shop='". $address ."'";
  $secondjk=$mysql->getOne($sql);
  // echo $secondjk;
  // exit;

  //备份,供删除
  $bak=$secondjk;

  //获取这个月第三周的人数
  $sql="select count(*) from alldev where whitelist <> '1' and timestamp >= $third and timestamp < $fourth and shop='". $address ."'";
  $thirdjk=$mysql->getOne($sql);
  // echo $thirdjk;
  // exit;

  //获取这个月第四周的人数
  $sql="select count(*) from alldev where whitelist <> '1' and timestamp >= $fourth and timestamp < $fifth and shop='". $address ."'";
  $fourthjk=$mysql->getOne($sql);



  //统计今年


  //获取第一季度开始时间戳
  $firstmonth =mktime(0,0,0,1,1,date('Y',time()));

  //获取第二季度开始时间
  $secondmonth =mktime(0,0,0,4,1,date('Y',time()));
  // echo date('Y-m-d H:i:s',$secondmonth);

  //获取第三季度开始时间
  $thirdmonth =mktime(0,0,0,7,1,date('Y',time()));
  // echo date('Y-m-d H:i:s',$thirdmonth);

  //获取第四季度开始时间
  $fourthmonth =mktime(0,0,0,10,1,date('Y',time()));
  // echo date('Y-m-d H:i:s',$fourthmonth);

  //获取明年第一季度开始时间
  $firstmonths =mktime(0,0,0,1,1,date('Y',time())+1);
  // echo date('Y-m-d H:i:s',$firstmonths);



  //获取第一季度的人数
  $sql="select count(*) from alldev where whitelist <> '1' and timestamp >= $firstmonth and timestamp < $secondmonth and shop='". $address ."'";
  $firstmonthjk=$mysql->getOne($sql);
  // echo $firstmonth;
  // exit;

  //获取第二季度的人数
  $sql="select count(*) from alldev where whitelist <> '1' and timestamp >= $secondmonth and timestamp < $thirdmonth and shop='". $address ."'";
  $secondmonthjk=$mysql->getOne($sql);

  //获取第三季度的人数
  $sql="select count(*) from alldev where whitelist <> '1' and timestamp >= $thirdmonth and timestamp < $fourthmonth and shop='". $address ."'";
  $thirdmonthjk=$mysql->getOne($sql);
  // echo $thirdmonth;
  // exit;


  //获取第四季度的人数
  $sql="select count(*) from alldev where whitelist <> '1' and timestamp >= $fourthmonth and timestamp < $firstmonths and shop='". $address ."'";
  $fourthmonthjk=$mysql->getOne($sql);

  // echo $fourthmonth;
  // exit;







  //首次今天首次进店人数,通过字段visittimes为1来进行判断
  $sql="select count(*) from alldev where whitelist <> '1' and visittimes=1 and timestamp > $start and timestamp < $end and shop='".$address ."'";
  $firstlogin=$mysql->getOne($sql);

  //统计当前销售区
  $sql="select count(*) from alldev where whitelist <> '1' and isOL=1 and timestamp > $mintime and timestamp < $maxtime and shop='" . $address ."' and area='展厅区' or '销售区' or '精品区'";
  $sale=$mysql->getOne($sql);


  //统计今天售后区
  $service=$allnow-$sale;


  //今天多次进店人数
  $sql="select count(*) from alldev where whitelist <> '1' and visittimes!=1 and timestamp > $start and timestamp < $end and shop='" . $address ."'";
  $second=$mysql->getOne($sql);

  //统计登陆人数
  $sql="select count(*) from alldev where whitelist <> '1' and isOL=1 and timestamp > $start and timestamp < $end and shop='" . $address ."'";
  $login=$mysql->getOne($sql);
  // echo $login;

  //统计未登陆人数
  $sql="select count(*) from alldev where whitelist <> '1' and isOL=2 and timestamp > $start and timestamp < $end and shop='" . $address ."'";
  $nolog=$mysql->getOne($sql);
  // echo $nolog;

  //统计历史进店人数
  $sql="select count(*) from alldev where whitelist <> '1' and shop='". $address ."'";
  $history=$mysql->getOne($sql);










  /*************************************************



  同区集客对比



  **************************************************/

  //就一个4s店,最大值和最小值,大区平均值是一样的都为$alladay;

  //本周4s店最大值

  //统计本周
  //获取本周周一的时间
  $one=strtotime('-1 monday', time());
  // echo $one . '<br />';
  // echo date('Y-m-d H:i:s',$one);
  // exit;

  //获取下周周一
  $nowone=strtotime('-1 monday', time())+24*3600*7;
  // echo date('Y-m-d H:i:s',$nowone);

  $sql="select count(*) from alldev where whitelist <> '1' and timestamp >= $one and timestamp < $nowone and shop='". $address ."'";
  $allweekjk=$mysql->getOne($sql);


  //统计本月
  //获取这个个月第一天时间
  $first =mktime(0,0,0,date('m',time()),1,date('Y',time()));
  // echo date('Y-m-d H:i:s',$first);
  // exit;

  //获取这个月第五周时间
  $fifth =mktime(0,0,0,date('m',time()),1,date('Y',time()));
  // echo date('Y-m-d H:i:s',$fifth);
  // exit;


  //获取这个月第一周的人数
  $sql="select count(*) from alldev where whitelist <> '1' and timestamp >= $first and timestamp < $fifth and shop='". $address ."'";
  $allmonthjk=$mysql->getOne($sql);
  // echo $firstjk;
  // exit;


  //统计今年

  //获取第一季度开始时间戳
  $firstmonth =mktime(0,0,0,1,1,date('Y',time()));

  //获取明年第一季度开始时间
  $firstmonths =mktime(0,0,0,1,1,date('Y',time())+1);
  // echo date('Y-m-d H:i:s',$firstmonths);

  $sql="select count(*) from alldev where whitelist <> '1' and timestamp >= $firstmonth and timestamp < $firstmonths and shop='". $address ."'";
  $allyearjk=$mysql->getOne($sql);

  /********************************************************************

  全国集客对比


  ***********************************************************************/


  /*****************************
  	今天

  *******************/

  //最大值为,先根据店进行分组,然后再统计每个组的值,再取出最大值
  $sql="select shop,count(shop) from alldev where whitelist <> '1' and timestamp > $start and timestamp < $end and shop <> 'Audi City BeiJing' group by shop";
  $arr=$mysql->getAll($sql);

  $disArr = array();
  foreach($arr as $value) {
  	$disArr[] = floatval($value['count(shop)']);
  }
  sort($disArr);
  $resArr = !empty($disArr) ? array($disArr[0],$disArr[count($disArr)-1]) : array(10,10);
  // print_r($resArr);




  //最大值
  $pos = array_search(max($resArr), $resArr);
  $maxjk=$resArr[$pos];


  //最小值
  $pos = array_search(min($resArr), $resArr);
  $minjk=$resArr[$pos];


  //大区平均值和今天的值一样$allday


  //本店值月上述的一样

  //全国今天的值
  $sql="select count(*) from alldev where whitelist <> '1' and timestamp > $start and timestamp < $end and shop <> 'Audi City BeiJing'";
  $alldaycountry=$mysql->getOne($sql);
  // echo $alldaycountry;
  // exit;
  $sql="select shop from alldev group by shop";
  $num=$mysql->getAll($sql);
  // print_r($num);
  $num=count($num)-1;
  // echo $num;
  // exit;
  //全国今天的平均值
  $daycountry=ceil($alldaycountry/$num);
  // echo $daycountry;
  // exit;



  /*****************************
  	本周

  *******************/
  //统计本周
  //获取本周周一的时间
  /*$one=strtotime('-1 monday', time());
  // echo $one . '<br />';
  // echo date('Y-m-d H:i:s',$one);
  // exit;

  //获取下周周一
  $nowone=strtotime('-1 monday', time())+24*3600*7;
  // echo date('Y-m-d H:i:s',$nowone);




  //最大值为,先根据店进行分组,然后再统计每个组的值,再取出最大值
  $sql="select shop,count(shop) from alldev where whitelist <> '1' and timestamp > $one and timestamp < $nowone and shop <> 'Audi City BeiJing' group by shop";
  $arr=$mysql->getAll($sql);

  $disArr = array();
  foreach($arr as $value) {
  	$disArr[] = floatval($value['count(shop)']);
  }
  sort($disArr);
  $resArr = !empty($disArr) ? array($disArr[0],$disArr[count($disArr)-1]) : array(10,10);
  // print_r($resArr);




  //最大值
  $pos = array_search(max($resArr), $resArr);
  $weekmaxjk=$resArr[$pos];


  //最小值
  $pos = array_search(min($resArr), $resArr);
  $weekminjk=$resArr[$pos];


  //大区平均值和今天的值一样$allday


  //本店值月上述的一样

  //全国本月的值
  $sql="select count(*) from alldev where whitelist <> '1' and timestamp > $one and timestamp < $nowone and shop <> 'Audi City BeiJing'";
  $allweekcountry=$mysql->getOne($sql);
  // echo $alldaycountry;
  // exit;
  $sql="select shop from alldev group by shop";
  $num=$mysql->getAll($sql);
  // print_r($num);
  $num=count($num)-1;
  // echo $num;
  // exit;
  //全国今天的平均值
  $weekcountry=ceil($allweekcountry/$num);
  // echo $daycountry;
  // exit;*/














  /***********************

  	本月

  *************************/


  /*//统计本月
  //获取这个个月第一天时间
  $first =mktime(0,0,0,date('m',time()),1,date('Y',time()));
  // echo date('Y-m-d H:i:s',$first);
  // exit;

  //获取这个月第五周时间
  $fifth =mktime(0,0,0,date('m',time()),1,date('Y',time()));
  // echo date('Y-m-d H:i:s',$fifth);
  // exit;






  //最大值为,先根据店进行分组,然后再统计每个组的值,再取出最大值
  $sql="select shop,count(shop) from alldev where whitelist <> '1' and timestamp > $first and timestamp < $fifth and shop <> 'Audi City BeiJing' group by shop";
  $arr=$mysql->getAll($sql);

  $disArr = array();
  foreach($arr as $value) {
  	$disArr[] = floatval($value['count(shop)']);
  }
  sort($disArr);
  $resArr = !empty($disArr) ? array($disArr[0],$disArr[count($disArr)-1]) : array(10,10);
  // print_r($resArr);




  //最大值
  $pos = array_search(max($resArr), $resArr);
  $monthmaxjk=$resArr[$pos];


  //最小值
  $pos = array_search(min($resArr), $resArr);
  $monthminjk=$resArr[$pos];


  //大区平均值和今天的值一样$allday


  //本店值月上述的一样

  //全国本月的值
  $sql="select count(*) from alldev where whitelist <> '1' and timestamp > $first and timestamp < $fifth and shop <> 'Audi City BeiJing'";
  $allmonthcountry=$mysql->getOne($sql);
  // echo $alldaycountry;
  // exit;
  $sql="select shop from alldev group by shop";
  $num=$mysql->getAll($sql);
  // print_r($num);
  $num=count($num)-1;
  // echo $num;
  // exit;
  //全国今天的平均值
  $monthcountry=ceil($allmonthcountry/$num);
  // echo $daycountry;
  // exit;
  */












  /***********************

  	今年


  ***********************/

  /*//获取第一季度开始时间戳
  $firstmonth =mktime(0,0,0,1,1,date('Y',time()));

  //获取明年第一季度开始时间
  $firstmonths =mktime(0,0,0,1,1,date('Y',time())+1);
  // echo date('Y-m-d H:i:s',$firstmonths);








  //最大值为,先根据店进行分组,然后再统计每个组的值,再取出最大值
  $sql="select shop,count(shop) from alldev where whitelist <> '1' and timestamp > $firstmonth and timestamp < $firstmonths and shop <> 'Audi City BeiJing' group by shop";
  $arr=$mysql->getAll($sql);

  $disArr = array();
  foreach($arr as $value) {
  	$disArr[] = floatval($value['count(shop)']);
  }
  sort($disArr);
  $resArr = !empty($disArr) ? array($disArr[0],$disArr[count($disArr)-1]) : array(10,10);
  // print_r($resArr);




  //最大值
  $pos = array_search(max($resArr), $resArr);
  $yearmaxjk=$resArr[$pos];


  //最小值
  $pos = array_search(min($resArr), $resArr);
  $yearminjk=$resArr[$pos];


  //大区平均值和今天的值一样$allday


  //本店值月上述的一样

  //全国本月的值
  $sql="select count(*) from alldev where whitelist <> '1' and timestamp > $firstmonth and timestamp < $firstmonths and shop <> 'Audi City BeiJing'";
  $allyearcountry=$mysql->getOne($sql);
  // echo $alldaycountry;
  // exit;
  $sql="select shop from alldev group by shop";
  $num=$mysql->getAll($sql);
  // print_r($num);
  $num=count($num)-1;
  // echo $num;
  // exit;
  //全国今天的平均值
  $yearcountry=ceil($allyearcountry/$num);
  // echo $daycountry;
  // exit;
  */



  /*************************************

  	进店频次(每天统计一次,今天累计人数的进店频次)


  ************************************/
  //统计当天累计人数数
  $t = time();
  $start = mktime(0,0,0,date("m",$t),date("d",$t),date("Y",$t));
  $end = mktime(23,59,59,date("m",$t),date("d",$t),date("Y",$t));
  $sql="select count(*) from alldev where whitelist <> '1' and timestamp > $start and timestamp < $end and shop='". $address ."'";
  $allday=$mysql->getOne($sql);

  //统计进店频次为2次的
  $sql="select count(*) from alldev where whitelist <> '1' and timestamp > $start and timestamp < $end  and visittimes='2' and shop='". $address ."'";
  $visittwo=$mysql->getOne($sql);
  // echo $visittwo;
  // exit;

  //统计进店频次为3次
  $sql="select count(*) from alldev where whitelist <> '1' and timestamp > $start and timestamp < $end  and visittimes='3' and shop='". $address ."'";
  $visitthree=$mysql->getOne($sql);
  // echo $visitthree;
  // exit;



  //统计进店频次为3次以上
  $sql="select count(*) from alldev where whitelist <> '1' and timestamp > $start and timestamp < $end  and visittimes > '3' and shop='". $address ."'";
  $visitmore=$mysql->getOne($sql);
  // echo $visitmore;
  // exit;

  //计算频次

  //两次
  $vtwo=ceil(($visittwo/$allday)*100);
  // echo $vtwo;
  // exit;
  //三次
  $vthree=ceil(($visitthree/$allday)*100);
  // echo $vthree;
  // exit;

  //三次以上
  $vmore=ceil(($visitmore/$allday)*100);
  // echo $vmore;
  // exit;

  //一次
  $vone=100-$vtwo-$vthree-$vmore;
  // echo $vone;
  // exit;



  switch ($address) {
    case '义乌市博奥汽车销售服务有限公司':
      $hash='SubArea_MarketDirector_ywba';
      break;
    case '广东粤奥汽车销售服务有限公司':
      $hash='SubArea_MarketDirector_gdya';
      break;
    case '辽宁鑫迪汽车销售服务有限公司':
      $hash='SubArea_MarketDirector_lnxd';
      break;
    case '江苏天奥汽车销售服务有限公司':
      $hash='SubArea_MarketDirector_jsta';
      break;
    case '成都新元素雅麓汽车销售服务有限公司':
      $hash='SubArea_MarketDirector_cdyl';
      break;
    case '云南驰泰汽车销售服务有限公司':
      $hash='SubArea_MarketDirector_ynct';
      break;
    case '宁夏奥立升汽车销售服务有限公司':
      $hash='SubArea_MarketDirector_nxals';
      break;
    case '陕西奥诚汽车销售服务有限公司':
      $hash='SubArea_MarketDirector_sxac';
      break;
    case '北京博瑞祥星汽车销售有限公司':
      $hash='SubArea_MarketDirector_brxx';
      break;
  }
  $redis = new Redis();
    //连接服务器
    $redis->connect("localhost");
    //授权
    $redis->auth("redis=pwd@2016");
  $redis->hmset($hash,
  array(
    'allnow' =>$allnow,
    'allday'=>$allday,
    'yesdallday'=>$yesdallday,
    'onejk'=>$onejk,
    'twojk'=>$twojk,
    'threejk'=>$threejk,
    'fourjk'=>$fourjk,
    'fivejk'=>$fivejk,
    'sixjk'=>$sixjk,
    'sevenjk'=>$sevenjk,
    'firstjk'=>$firstjk,
    'secondjk'=>$secondjk,
    'thirdjk'=>$thirdjk,
    'fourthjk'=>$fourthjk,
    'firstmonthjk'=>$firstmonthjk,
    'secondmonthjk'=>$secondmonthjk,
    'thirdmonthjk'=>$thirdmonthjk,
    'fourthmonthjk'=>$fourthmonthjk,
    'allweekjk'=>$allweekjk,
    'allmonthjk'=>$allmonthjk,
    'allyearjk'=>$allyearjk,
    'maxjk'=>$maxjk,
    'minjk'=>$minjk,
    'alldaycountry'=>$alldaycountry,
    'daycountry'=>$daycountry,
    'vtwo'=>$vtwo,
    'vthree'=>$vthree,
    'vmore'=>$vmore,
    'vone'=>$vone,
  ));
}
